﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;

namespace StoredProcedureGenerator
{
    /// <summary>
    /// Stored Procedure generator class to MSSQL database
    /// </summary>
    public class MSSQL_GenerateStoredProceduresClass
    {
        #region private fields
        private string _ConnectionString;

        private string _InsertSpTemplateFile;
        private string _UpdateSpTemplateFile;
        private string _DeleteSpTemplateFile;

        private string _OutputFilesPath;
        #endregion

        #region public properties
        public string ConnectionString
        {
            get { return _ConnectionString; }
            set { _ConnectionString = value; }
        }

        public string InsertSpTemplateFile
        {
            get { return _InsertSpTemplateFile; }
            set { _InsertSpTemplateFile = value; }
        }

        public string UpdateSpTemplateFile
        {
            get { return _UpdateSpTemplateFile; }
            set { _UpdateSpTemplateFile = value; }
        }
    
        public string DeleteSpTemplateFile
        {
            get { return _DeleteSpTemplateFile; }
            set { _DeleteSpTemplateFile = value; }
        }

        public string OutputFilesPath
        {
            get { return _OutputFilesPath; }
            set { _OutputFilesPath = value; }
        }
        #endregion

        #region konstruktorok
    
        public MSSQL_GenerateStoredProceduresClass()
        {
        }

        public MSSQL_GenerateStoredProceduresClass(string connectionString)
        {
            _ConnectionString = connectionString;
        }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="insertTemplateFile"></param>
        /// <param name="updateTemplateFile"></param>
        /// <param name="deleteTemplateFile"></param>
        public MSSQL_GenerateStoredProceduresClass(string connectionString, 
            string insertTemplateFile, 
            string updateTemplateFile, 
            string deleteTemplateFile)
        {
            this._ConnectionString = connectionString;

            this._InsertSpTemplateFile = insertTemplateFile;
            this._UpdateSpTemplateFile = updateTemplateFile;
            this._DeleteSpTemplateFile = deleteTemplateFile;
            
        }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="insertTemplateFile"></param>
        /// <param name="updateTemplateFile"></param>
        /// <param name="deleteTemplateFile"></param>
        /// <param name="outputFilePath">path for Output files</param>
        public MSSQL_GenerateStoredProceduresClass(string connectionString,
            string insertTemplateFile,
            string updateTemplateFile,
            string deleteTemplateFile,
            string outputFilePath)
        {
            this._ConnectionString = connectionString;

            this._InsertSpTemplateFile = insertTemplateFile;
            this._UpdateSpTemplateFile = updateTemplateFile;
            this._DeleteSpTemplateFile = deleteTemplateFile;
            this._OutputFilesPath = outputFilePath;

        }

        #endregion

        /// <summary>
        /// Call the all sp generate method
        /// </summary>
        public void GenerateStoredProcedures()
        {
            if (String.IsNullOrEmpty(_ConnectionString))
                throw new Exception("The ConnectionString is NULL or EMPTY!");

            if (String.IsNullOrEmpty(_OutputFilesPath))
            {
                _OutputFilesPath = @"storedProcedures";
            }
            
            InsertSP();
            UpdateSP();
            DeleteSP();
        }

        #region first work to SP generate
        private void InsertSP()
        {
            if (String.IsNullOrEmpty(_ConnectionString))
                return;
            if (String.IsNullOrEmpty(_InsertSpTemplateFile))
                return;
            if (!File.Exists(_InsertSpTemplateFile))
            {
                string message = "\nThe Insert template file is not exist!";
                //throw new Exception(message);
                Console.WriteLine(message);
                return;
            }

            SqlConnection sqlConn = new SqlConnection(_ConnectionString);
            SqlDataReader sdr = null;
            try
            {
                sqlConn.Open();

                
                SqlCommand command = new SqlCommand(QueryHelper.SelectTablesQuery, sqlConn);
                sdr = command.ExecuteReader();

                Dictionary<string, string> template = new Dictionary<string, string>();
                string tableName = "";
                string parameterList = "";
                string collumnList = "";
                string valueList = "";

                while (sdr.Read())
                {

                    if (sdr[0].ToString() != tableName && tableName != "")
                    {
                        generateInsertSp(tableName, parameterList, collumnList, valueList);
                        tableName = "";
                        parameterList = "";
                        collumnList = "";
                        valueList = "";
                    }

                    tableName = sdr[0].ToString();

                    //sdr[0] - TableName.
                    //sdr[1] - CollumnName
                    //sdr[2] - collumn type
                    //sdr[3] - length
                    //sdr[4] - IsIdentity (0||1)
                    //Get the primary key
                    List<string> primaryKeyList = getPrimaryKey(sdr[0].ToString());

                    if (!primaryKeyList.Contains(sdr[1].ToString()) && Convert.ToInt32(sdr[4].ToString()) != 1)
                    {
                        if (collumnList != "")
                        {
                            collumnList += ",\n";
                        }
                        collumnList += "\t" + sdr[1].ToString();  //collumnName

                        if (parameterList != "")
                        {
                            parameterList += ",\n";
                        }
                        parameterList += "\t" + "@" + sdr[1].ToString() + " " + sdr[2].ToString() +
                            ((sdr[3].ToString() != "") ? (" (" + sdr[3].ToString() + ")") : "")
                            ;

                        if (valueList != "")
                        {
                            valueList += ",\n";
                        }
                        valueList += "\t" + "@" + sdr[1].ToString();

                    }

                    for (int i = 0; i < sdr.FieldCount; i++)
                    {
                        
                        Console.Write(sdr[i] + " ");

                        if (primaryKeyList.Contains(sdr[i].ToString()))
                            Console.Write("Primary_Key ");
                    }
                    Console.Write("\n");
                }
                generateInsertSp(tableName, parameterList, collumnList, valueList);
              
            }
            catch (Exception ex)
            {
                throw ex;
            }
            #region finally
            finally
            {
                // close the reader
                if (sdr != null)
                {
                    sdr.Close();
                }

                // Close the connection
                if (sqlConn != null)
                {
                    sqlConn.Close();
                }
            }
            #endregion

        }
        private void UpdateSP()
        {
            if (String.IsNullOrEmpty(_ConnectionString))
                return;
            if (String.IsNullOrEmpty(_UpdateSpTemplateFile))
                return;

            if (!File.Exists(_UpdateSpTemplateFile))
            {
                string message = "\nThe Update template file is not exists!";
                //throw new Exception(message);
                Console.WriteLine(message);
                return;
            }

            SqlConnection sqlConn = new SqlConnection(_ConnectionString);
            SqlDataReader sdr = null;
            try
            {
                sqlConn.Open();

                SqlCommand command = new SqlCommand(QueryHelper.SelectTablesQuery, sqlConn);
                sdr = command.ExecuteReader();

                Dictionary<string, string> template = new Dictionary<string, string>();
                string tableName = "";
                string parameterList = "";
                string whereList = "";
                string setList = "";

                while (sdr.Read())
                {
                    if (sdr[0].ToString() != tableName && tableName != "")
                    {
                        generateUpdateSp(tableName, parameterList, setList, whereList);
                        template = new Dictionary<string, string>();
                        tableName = "";
                        parameterList = "";
                        whereList = "";
                        setList = "";
                    }

                    tableName = sdr[0].ToString();

                    //sdr[0] - TableName.
                    //sdr[1] - CollumnName
                    //sdr[2] - collumn type
                    //sdr[3] - length
                    //sdr[4] - IsIdentity (0||1)
                    //Get the primary key
                    List<string> primaryKeyList = getPrimaryKey(sdr[0].ToString());


                    if (parameterList != "")
                    {
                        parameterList += ",\n";
                    }
                    parameterList += "\t" + "@" + sdr[1].ToString() + " " + sdr[2].ToString() +
                        ((sdr[3].ToString() != "") ? (" (" + sdr[3].ToString() + ")") : "")
                        ;

                    //if collumn is primary key, it can't modify
                    //TODO
                    if (!primaryKeyList.Contains(sdr[1].ToString()))
                    {

                        if (setList != "")
                        {
                            setList += ",\n";
                        }
                        setList += "\t" + sdr[1].ToString() + " = " + "@" + sdr[1].ToString();

                    }
                    else
                    {
                        if (whereList != "")
                        {
                            whereList += " AND\n";
                        }
                        else
                        {
                            whereList += "WHERE\n";
                        }
                        whereList += sdr[1].ToString() + " = @" + sdr[1].ToString();
                        
                    }

                    for (int i = 0; i < sdr.FieldCount; i++)
                    {

                        Console.Write(sdr[i] + " ");


                    }
                    Console.Write("\n");
                }
                generateUpdateSp(tableName, parameterList, setList, whereList);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            #region finally
            finally
            {
                // close the reader
                if (sdr != null)
                {
                    sdr.Close();
                }

                // Close the connection
                if (sqlConn != null)
                {
                    sqlConn.Close();
                }
            }
            #endregion
        }
        private void DeleteSP()
        {
            if (String.IsNullOrEmpty(_ConnectionString))
                return;
            if (String.IsNullOrEmpty(_DeleteSpTemplateFile))
                return;
            if (!File.Exists(_DeleteSpTemplateFile))
            {
                string message = "\nThe Delete template file is not exists";
                //throw new Exception(message);
                Console.WriteLine(message);
                return;
            }

            SqlConnection sqlConn = new SqlConnection(_ConnectionString);
            SqlDataReader sdr = null;
            try
            {
                sqlConn.Open();

                //get tables
                SqlCommand command = new SqlCommand(QueryHelper.SelectTablesQuery, sqlConn);
                sdr = command.ExecuteReader();

                Dictionary<string, string> template = new Dictionary<string, string>();
                string tableName = "";
                string parameterList = "";
                string prKeyList = "";

                while (sdr.Read())
                {
                    if (sdr[0].ToString() != tableName && tableName != "")
                    {
                        generateDeleteSp(tableName, parameterList, prKeyList);
                        tableName = "";
                        parameterList = "";
                        prKeyList = "";
                    }

                    tableName = sdr[0].ToString();

                    //sdr[0] - TableName.
                    //sdr[1] - CollumnName
                    //sdr[2] - collumn type
                    //sdr[3] - length
                    //Get the primary key
                    List<string> primaryKeyList = getPrimaryKey(sdr[0].ToString());

                    if (primaryKeyList.Contains(sdr[1].ToString()))
                    {

                        if (parameterList != "")
                        {
                            parameterList += ",\n";
                        }
                        parameterList += "\t" + "@" + sdr[1].ToString() + " " + sdr[2].ToString() +
                            ((sdr[3].ToString() != "") ? (" (" + sdr[3].ToString() + ")") : "")
                            ;

                        if (prKeyList != "")
                        {
                            prKeyList += " AND\n";
                        }
                        prKeyList += "\t" + sdr[1].ToString() + " = @" + sdr[1].ToString();
                    }
                    for (int i = 0; i < sdr.FieldCount; i++)
                    {

                        Console.Write(sdr[i] + " ");

                        if (primaryKeyList.Contains(sdr[i].ToString()))
                            Console.Write("Primary_Key ");
                    }
                    Console.Write("\n");
                }
                generateDeleteSp(tableName, parameterList, prKeyList);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            #region finally
            finally
            {
                // close the reader
                if (sdr != null)
                {
                    sdr.Close();
                }

                // Close the connection
                if (sqlConn != null)
                {
                    sqlConn.Close();
                }
            }
            #endregion
        }
        #endregion

        #region generate SP
        private void generateInsertSp(string tableName, string parameterList, string collumnList, string valueList)
        {
            if (String.IsNullOrEmpty(_InsertSpTemplateFile))
                return;
            if (!File.Exists(_InsertSpTemplateFile))
            {
                string message = "The Insert template file is not exists";
                //throw new Exception(message);
                Console.WriteLine(message);
                return;
            }

            Dictionary<string, string> template = new Dictionary<string,string>();
            string procedureName = tableName + "_Insert";

            template.Add("procedureName", procedureName);
            template.Add("parameterList", parameterList);
            template.Add("columnList", collumnList);
            template.Add("valueList", valueList);
            template.Add("tableName", tableName);
            try
            {
                string sp = StringHelper.ReplaceString(_InsertSpTemplateFile, template);
                WriteSp(sp, procedureName);
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        private void generateUpdateSp(string tableName, string parameterList, string setList, string whereList)
        {
            if (String.IsNullOrEmpty(_UpdateSpTemplateFile))
                return;

            if (!File.Exists(_UpdateSpTemplateFile))
            {
                string message = "The Update template file is not exists";
                //throw new Exception(message);
                Console.WriteLine(message);
                return;
            }

            Dictionary<string, string> template = new Dictionary<string, string>();
            string procedureName = tableName + "_UpdateByPrimaryKeys";

            template.Add("procedureName", procedureName);
            template.Add("parameterList", parameterList);
            template.Add("setList", setList);
            template.Add("tableName", tableName);
            template.Add("whereList", whereList);
            try
            {
                string sp = StringHelper.ReplaceString(_UpdateSpTemplateFile, template);

                WriteSp(sp, procedureName);
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        private void generateDeleteSp(string tableName, string parameterList, string primaryKeyList)
        {
            if (String.IsNullOrEmpty(_DeleteSpTemplateFile))
                return;
            if (!File.Exists(_DeleteSpTemplateFile))
            {
                string message = "The Delete template file is not exists";
                //throw new Exception(message);
                Console.WriteLine(message);
                return;
            }

            Dictionary<string, string> template = new Dictionary<string, string>();
            if (primaryKeyList == "")
                return;

            string procedureName = tableName + "_DeleteByPrimaryKeys";
            template.Add("procedureName", procedureName);
            template.Add("parameterList", parameterList);
            template.Add("primaryKeyList", primaryKeyList);
            template.Add("tableName", tableName);

            try
            {
                //Make sp
                string sp = StringHelper.ReplaceString(_DeleteSpTemplateFile, template);
                WriteSp(sp, procedureName);
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        #endregion

        /// <summary>
        /// Write the generated sp to File
        /// </summary>
        /// <param name="sp"></param>
        /// <param name="fileName"></param>
        private void WriteSp(string sp, string fileName)
        {
            Console.Write("\n\n");
            Console.WriteLine(sp);

            try
            {
                if (!Directory.Exists(_OutputFilesPath))
                {
                    Directory.CreateDirectory(_OutputFilesPath);
                }

                // create a writer and open the file
                TextWriter tw = new StreamWriter(this._OutputFilesPath + @"\" + fileName + ".storedProcedure.sql",false);

                // write a line of text to the file
                tw.WriteLine(sp);

                // close the stream
                tw.Close();
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// Get the Primary keys from table
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private List<string> getPrimaryKey(string tableName)
        {
            SqlConnection sqlConn = new SqlConnection(_ConnectionString);
            SqlDataReader sdr = null;
            List<string> resultList = new List<string>();
            try
            {
                sqlConn.Open();

                SqlCommand command = new SqlCommand(QueryHelper.GetPrimaryKeyByTableNameQuery(tableName), sqlConn);
                sdr = command.ExecuteReader();

                while (sdr.Read())
                {
                    resultList.Add(sdr[0].ToString());
                }          
            }
            finally
            {
                if (sdr != null)
                {
                    sdr.Close();
                }

                if (sqlConn != null)
                {
                    sqlConn.Close();
                }
            }
            return resultList;
        }
    }
}
